/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package persistence;


import Message.Message;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Observable;

/**
 *
 * @author mmcfadye
 */
public class mysqlAdapter extends Observable {

    public mysqlAdapter(){setChanged();}

    @Override
    protected synchronized void clearChanged()
    {
	setChanged();
    }

    /**
     * creates a connection to our database.
     * @return returns the connection.
     */
    public Connection connect()
    {
	Connection conn = null;
	try
	{
	    // The newInstance() call is a work around for some
	    // broken Java implementations

	    Class.forName("org.gjt.mm.mysql.Driver").newInstance();
	}
	catch (Exception E)
	{
	    System.err.println("Unable to load driver.");
	    E.printStackTrace();
	}

	try
	{
	    // Obtain a connection from the driver manager.
	    conn =
	    DriverManager.getConnection(
		    "jdbc:mysql://savage.augustana.ca:3306/uberhockey?user=uberhockey&password=uberhockey");
	}
	catch (SQLException E)
	{
	    System.out.println("SQLException: " + E.getMessage());
	    System.out.println("\nSQLState:     " + E.getSQLState());
	    System.out.println("\nVendorError:  " + E.getErrorCode());
	}

	return conn;
    }

        public void newLeague(String leagueID, String leagueName, float salary, int rosterSize)
    {
	Connection conn = connect();

        int output = 0;


	try
	{
	    Statement stmt = conn.createStatement();


	    output = stmt.executeUpdate("INSERT INTO League VALUES" +
					"('" + leagueID + "', '" + leagueName + "', '" + salary +
                                        "' , '" + rosterSize + "')");
	}
	catch (SQLException E)
	{
	    String toReturn = "";
	    toReturn += "\nSQLException: " + E.getMessage();
	    toReturn += "\nSQLState:     " + E.getSQLState();
	    toReturn += "\nVendorError:  " + E.getErrorCode();
            notifyObservers(new Message("error", toReturn));
            return;
	}

        notifyObservers(new Message("success", "League successfully added."));
    }

    public void newSkaterScoutingReport(int scoutID, int playerID, int hockeySense, int puckhandlin, int defense, int shot, int poise, int size, int leadership, int skating, int potential, java.sql.Date date) {
        Connection conn = connect();
	try
	{
	    Statement stmt = conn.createStatement();


	    stmt.executeUpdate("INSERT INTO ScoutSkaterReport VALUES" +
					"('" + scoutID + "', '" + playerID + "', '" + hockeySense +
                                        "' , '" + puckhandlin + "', '" + defense + "', '" + shot +
                                        "', '" + poise + "', '" + size + "', '" + leadership  +
                                        "', '" + skating +  "', '" + potential +
                                        "', '" + date + "')");
	}
	catch (SQLException E)
	{
            String toReturn = "";
	    toReturn += "\nSQLException: " + E.getMessage();
	    toReturn += "\nSQLState:     " + E.getSQLState();
	    toReturn += "\nVendorError:  " + E.getErrorCode();
            notifyObservers(new Message("error", toReturn));
            return;
	}

        notifyObservers(new Message("success", "Scouting report successfully added."));
    }

    public void retirePlayer(int playerID) {
        Connection conn = connect();
	try
	{
	    Statement stmt = conn.createStatement();


	    stmt.executeUpdate("DELETE FROM Player WHERE playerID='" + playerID+"'");
	}
	catch (SQLException E)
	{
            String toReturn = "";
	    toReturn += "\nSQLException: " + E.getMessage();
	    toReturn += "\nSQLState:     " + E.getSQLState();
	    toReturn += "\nVendorError:  " + E.getErrorCode();
            notifyObservers(new Message("error", toReturn));
            return;
	}

        notifyObservers(new Message("success", "Player successfully retired."));
    }

    public void getScoutingReports(int playerID) {
        Connection conn = connect();

        String toSuccessReturn = "";

	try
	{
	    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );


	    ResultSet resultSet =
                    stmt.executeQuery("SELECT * FROM ScoutSkaterReport r, Player p, Staff s " +
                    "WHERE r.skaterID = p.playerID " +
                    "AND r.scoutID = s.staffID " +
                    "AND p.playerID=" + playerID);

            while (resultSet.next())
                toSuccessReturn += "Scout Name: " + resultSet.getString("staffName") + "\n"
                                + "Player Name: "+ resultSet.getString("playerName") + "\n"
                                + "Report Date: " + resultSet.getString("reportDate") + "\n"
                                + "Hockey Sense: " + resultSet.getString(3) + "\n"
                                + "Puckhandling: " + resultSet.getString(4) + "\n"
                                + "Defense: " + resultSet.getString(5) + "\n"
                                + "Shot / Scoring: " + resultSet.getString(6) + "\n"
                                + "Poise: " + resultSet.getString(7) + "\n"
                                + "Size / Strength: " + resultSet.getString(8) + "\n"
                                + "Leadership: "+ resultSet.getString(9) + "\n"
                                + "Skating: "+ resultSet.getString(10) + "\n"
                                + "Potential: "+ resultSet.getString(11) + "\n"
                                + "--------------------------------------------\n";
	}
	catch (SQLException E)
	{
            String toExceptionReturn = "";
	    toExceptionReturn += "SQLException: " + E.getMessage();
	    toExceptionReturn += "\nSQLState:     " + E.getSQLState();
	    toExceptionReturn += "\nVendorError:  " + E.getErrorCode();
            notifyObservers(new Message("error", toExceptionReturn));
            return;
	}

        notifyObservers(new Message("scoutingReport", toSuccessReturn));
    }

    public void hireStaff(String teamID, int staffID, String date, int salary) {
        Connection conn = connect();
	try
	{
	    Statement stmt = conn.createStatement();


	    stmt.executeUpdate("INSERT INTO TeamStaff VALUES" +
					"('" + staffID + "', '" +  teamID + "', '" + date +
                                        "' , '" + salary + "')");
	}
	catch (SQLException E)
	{
            String toReturn = "";
	    toReturn += "\nSQLException: " + E.getMessage();
	    toReturn += "\nSQLState:     " + E.getSQLState();
	    toReturn += "\nVendorError:  " + E.getErrorCode();
            notifyObservers(new Message("error", toReturn));
            return;
	}

        notifyObservers(new Message("success", "Staff successfully hired."));
    }

    public void viewTeamStaff(String teamID) {
        Connection conn = connect();

        String toSuccessReturn = "";

	try
	{
	    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );


	    ResultSet resultSet =
                    stmt.executeQuery("SELECT * FROM TeamStaff ts, Staff s, Team t " +
                    "WHERE ts.staffID = s.staffID " +
                    "AND ts.teamID = t.teamID "
                    +"AND ts.teamID = '" + teamID +"'");

            while (resultSet.next())
                toSuccessReturn += "Staff Name: " + resultSet.getString("staffName") + "\n"
                                + "Team Name: "+ resultSet.getString("teamName") + "\n"
                                + "Contract End Date: " + resultSet.getString(3) + "\n"
                                + "Salary: " + resultSet.getString(4) + "\n"
                                + "--------------------------------------------\n";
	}
	catch (SQLException E)
	{
            String toExceptionReturn = "";
	    toExceptionReturn += "SQLException: " + E.getMessage();
	    toExceptionReturn += "\nSQLState:     " + E.getSQLState();
	    toExceptionReturn += "\nVendorError:  " + E.getErrorCode();
            notifyObservers(new Message("error", toExceptionReturn));
            return;
	}

        notifyObservers(new Message("teamStaff", toSuccessReturn));
    }

    public void updateLeagueSalaryCap(String leagueID, int salaryCap) {
        Connection conn = connect();

	try
	{
	    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );


	    stmt.executeUpdate("UPDATE League SET salaryCap = " + salaryCap +
                               " WHERE leagueID = '" + leagueID + "'");

	}
	catch (SQLException E)
	{
            String toExceptionReturn = "";
	    toExceptionReturn += "SQLException: " + E.getMessage();
	    toExceptionReturn += "\nSQLState:     " + E.getSQLState();
	    toExceptionReturn += "\nVendorError:  " + E.getErrorCode();
            notifyObservers(new Message("error", toExceptionReturn));
            return;
	}

        notifyObservers(new Message("success", "League salary cap successfully updated."));
    }

    public void getGameReport(String away, String home, String date) {
        Connection conn = connect();

        String toSuccessReturn = "";

	try
	{
	    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );


	    ResultSet resultSet =
                    stmt.executeQuery("SELECT * FROM Game, Goal, Player, Roster " +
                    "WHERE Game.homeID = '" + home +"' " +
                    "AND Game.visitID = '" + away +"' " +
                    "AND Game.gameDate = '" + date + "' " +
                    "AND Game.homeID & Game.visitID & Game.gameDate = Goal.homeID & Goal.visitID & Goal.gameDate " +
                    "AND Goal.playerID = Player.PlayerID " +
                    "AND Player.PlayerID = Roster.PlayerID");


            while (resultSet.next())
            {
                toSuccessReturn += "Period of Goal: " + resultSet.getString("periodOfGoal") + "\t"
                                + "Time of Goal: "+ resultSet.getString("timeOfGoal") + "\n"
                                + "Scorer: " + resultSet.getString("playerName") + "\n";
                toSuccessReturn = getAssists(toSuccessReturn, home, away, date, resultSet.getString("playerID"), resultSet.getString("periodOfGoal"), resultSet.getString("timeOfGoal"));
                toSuccessReturn += "Team: " + resultSet.getString("teamID") + "\n"
                                + "--------------------------------------------\n";
            }
	}
	catch (SQLException E)
	{
            String toExceptionReturn = "";
	    toExceptionReturn += "SQLException: " + E.getMessage();
	    toExceptionReturn += "\nSQLState:     " + E.getSQLState();
	    toExceptionReturn += "\nVendorError:  " + E.getErrorCode();
            notifyObservers(new Message("error", toExceptionReturn));
            return;
	}

        notifyObservers(new Message("gameReport", toSuccessReturn));
    }

    public void filterPlayers(int parseInt, int parseInt0, int parseInt1, int parseInt2, int parseInt3, int parseInt4, int parseInt5, int parseInt6, int parseInt7) {
        Connection conn = connect();

        String toSuccessReturn = "";

	try
	{
            Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );


	    ResultSet resultSet =
                    stmt.executeQuery("SELECT * FROM ScoutSkaterReport ssr, Staff s, Player p " +
                    "WHERE hockeySense  >= " + parseInt + " " +
                    "AND puckhandling  >= " + parseInt0 + " " +
                    "AND defense  >= " + parseInt1 + " " +
                    "AND shotScoring  >= " + parseInt2 + " " +
                    "AND poise  >= " + parseInt3 + " " +
                    "AND sizeStrength  >= " + parseInt4 + " " +
                    "AND leadership  >= " + parseInt5 + " " +
                    "AND skating  >= " + parseInt6 + " " +
                    "AND potential  >= " + parseInt7 + " " +
                    "AND ssr.skaterID = p.playerID " +
                    "AND ssr.scoutID = s.staffID "
                    );


            while (resultSet.next())
                toSuccessReturn += "Scout Name: " + resultSet.getString("staffName") + "\t"
                                + "Player Name: " + resultSet.getString("playerName") + "\n"
                                + "Hockey Sense: " + resultSet.getString("hockeySense") + "\t"
                                + "Puck Handling: " + resultSet.getString("puckhandling") + "\t"
                                + "Defense: " + resultSet.getString("defense") + "\n"
                                + "Shot / Scoring: " + resultSet.getString("shotScoring") + "\t"
                                + "Poise: " + resultSet.getString("poise") + "\t\t"
                                + "Size / Strength: " + resultSet.getString("sizeStrength") + "\n"
                                + "Leadership: " + resultSet.getString("leadership") + "\t\t"
                                + "Skating: " + resultSet.getString("skating") + "\t\t"
                                + "Potential: " + resultSet.getString("potential") + "\n"
                                + "--------------------------------------------\n";
	}
	catch (SQLException E)
	{
            String toExceptionReturn = "";
	    toExceptionReturn += "SQLException: " + E.getMessage();
	    toExceptionReturn += "\nSQLState:     " + E.getSQLState();
	    toExceptionReturn += "\nVendorError:  " + E.getErrorCode();
            notifyObservers(new Message("error", toExceptionReturn));
            return;
	}

        notifyObservers(new Message("playerReport", toSuccessReturn));
    }

    public void viewTeamScoutingAbilities(String teamID) {
        Connection conn = connect();

        String toSuccessReturn = "";

	try
	{
	    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );


	    ResultSet resultSet =
                    stmt.executeQuery("SELECT * FROM TeamStaff ts, Staff s, Team t, Scout sc " +
                    "WHERE ts.staffID = s.staffID " +
                    "AND ts.teamID = t.teamID " +
                    "AND s.staffID = sc.scoutID " +
                    "AND ts.teamID = '" + teamID +"'");

            while (resultSet.next())
                toSuccessReturn += "Staff Name: " + resultSet.getString("staffName") + "\n"
                                + "Team Name: "+ resultSet.getString("teamName") + "\n"
                                + "Contract End Date: " + resultSet.getString(3) + "\n"
                                + "Salary: " + resultSet.getString(4) + "\n"
                                + "Ability Judgement: " + resultSet.getString("abilityJudgement") + "\n"
                                + "Potential Judgement: " + resultSet.getString("potentialJudgement") + "\n"
                                + "--------------------------------------------\n";
	}
	catch (SQLException E)
	{
            String toExceptionReturn = "";
	    toExceptionReturn += "SQLException: " + E.getMessage();
	    toExceptionReturn += "\nSQLState:     " + E.getSQLState();
	    toExceptionReturn += "\nVendorError:  " + E.getErrorCode();
            notifyObservers(new Message("error", toExceptionReturn));
            return;
	}

        notifyObservers(new Message("teamScouts", toSuccessReturn));
    }

    public void getNHLTeamAffiliations() {
        Connection conn = connect();

        String toSuccessReturn = "NHL Team\t\tAHL Team\n";

	try
	{
	    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );


	    ResultSet resultSet =
                    stmt.executeQuery("SELECT * FROM Affiliation a " +
                    "ORDER BY a.parentID");

            while (resultSet.next())
                toSuccessReturn += "Parent Team: " + resultSet.getString("parentID") + "\t"
                                + "Child Team: "+ resultSet.getString("childID") + "\n"
                                + "----------------------------------------------------------------------\n";
	}
	catch (SQLException E)
	{
            String toExceptionReturn = "";
	    toExceptionReturn += "SQLException: " + E.getMessage();
	    toExceptionReturn += "\nSQLState:     " + E.getSQLState();
	    toExceptionReturn += "\nVendorError:  " + E.getErrorCode();
            notifyObservers(new Message("error", toExceptionReturn));
            return;
	}

        notifyObservers(new Message("NHLTeamAffiliations", toSuccessReturn));
    }

    public void getTeamRoster(String text) {
        Connection conn = connect();

        String toSuccessReturn = "";

	try
	{
	    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );


	    ResultSet resultSet =
                    stmt.executeQuery("SELECT * FROM Roster r, Player p " +
                    "WHERE r.teamID = '" + text +"' " +
                    "AND r.playerID = p.playerID");

            while (resultSet.next())
                toSuccessReturn += "Player: " + resultSet.getString("playerName") + "\n"
                                + "----------------------------------------------------------------------\n";
	}
	catch (SQLException E)
	{
            String toExceptionReturn = "";
	    toExceptionReturn += "SQLException: " + E.getMessage();
	    toExceptionReturn += "\nSQLState:     " + E.getSQLState();
	    toExceptionReturn += "\nVendorError:  " + E.getErrorCode();
            notifyObservers(new Message("error", toExceptionReturn));
            return;
	}

        notifyObservers(new Message("teamRoster", toSuccessReturn));
    }

    public void getTeamProspects(String text) {
        Connection conn = connect();

        String toSuccessReturn = "";

	try
	{
	    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );


	    ResultSet resultSet =
                    stmt.executeQuery("SELECT * FROM Prospect pr, Player p " +
                    "WHERE pr.teamID = '" + text +"' " +
                    "AND pr.playerID = p.playerID");

            while (resultSet.next())
                toSuccessReturn += "Player: " + resultSet.getString("playerName") + "\n"
                                + "----------------------------------------------------------------------\n";
	}
	catch (SQLException E)
	{
            String toExceptionReturn = "";
	    toExceptionReturn += "SQLException: " + E.getMessage();
	    toExceptionReturn += "\nSQLState:     " + E.getSQLState();
	    toExceptionReturn += "\nVendorError:  " + E.getErrorCode();
            notifyObservers(new Message("error", toExceptionReturn));
            return;
	}

        notifyObservers(new Message("teamProspects", toSuccessReturn));
    }


private String getAssists(String toSuccessReturn, String home, String away, String date, String playerID, String periodOfGoal, String timeOfGoal) {

         Connection conn = connect();

	try
	{
	    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );


	    ResultSet resultSet =
                    stmt.executeQuery("SELECT * FROM Goal g, Player, Assist a " +
                    "WHERE a.homeID = '" + home +"' " +
                    "AND a.visitID = '" + away +"' " +
                    "AND a.gameDate = '" + date + "' " +
                    "AND a.goalScorerID = '" + playerID + "' " +
                    "AND a.periodOfGoal = '" + periodOfGoal + "' " +
                    "AND a.timeOfGoal = '" + timeOfGoal + "' " +
                    "AND g.homeID = a.homeID " +
                    "AND g.visitID = a.visitID " +
                    "AND g.gameDate = a.gameDate " +
                    "AND g.playerID = a.goalScorerID " +
                    "AND g.periodOfGoal = a.periodOfGoal " +
                    "AND g.timeOfGoal = a.timeOfGoal " +
                    "AND Player.playerID = a.assistScorerID");



            while (resultSet.next())
            {
                toSuccessReturn += "Assist: " + resultSet.getString("playerName") + "\n";
            }
        }

        catch (SQLException E)
	{
            String toExceptionReturn = "";
	    toExceptionReturn += "SQLException: " + E.getMessage();
	    toExceptionReturn += "\nSQLState:     " + E.getSQLState();
	    toExceptionReturn += "\nVendorError:  " + E.getErrorCode();
            notifyObservers(new Message("error", toExceptionReturn));
            return "";
	}

        return toSuccessReturn;
    }

}

